/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Codigo;

import java.awt.Desktop;
import java.awt.event.KeyEvent;
import java.awt.event.KeyListener;
import java.io.IOException;
import java.net.URI;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.PatternSyntaxException;
import javax.swing.ImageIcon;
import javax.swing.JOptionPane;
import javax.swing.RowFilter;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;

/**
 *
 * @author kyerte
 */
public class FrmMailing extends javax.swing.JFrame {

    ModeloTablaClientes modeloTabla;
    TableRowSorter<TableModel> sorter;
    ArrayList<String> correos;

    /**
     * Creates new form FrmMailing
     */
    public FrmMailing() {
        this.setIconImage(new ImageIcon(getClass().getResource("/Imagenes/email_icon_32.png")).getImage());
        initComponents();
        obtenerDesarrollos();

        modeloTabla = new ModeloTablaClientes();
        tablaResultados.setModel(modeloTabla);
        sorter = new TableRowSorter<TableModel>(modeloTabla);
        tablaResultados.setRowSorter(sorter);

        optEstatus.setSelected(true);
        buttonGroup1.add(optDesarrollo);
        buttonGroup1.add(optCorretaje);
        buttonGroup1.add(optEstatus);

        txtFiltro.addKeyListener(new KeyListener() {
            @Override
            public void keyTyped(KeyEvent e) {
                String texto = txtFiltro.getText();
                if (texto.length() == 0) {
                    sorter.setRowFilter(null);

                } else {
                    try {
                        sorter.setRowFilter(RowFilter.regexFilter(texto));
                    } catch (PatternSyntaxException ex) {
                        JOptionPane.showMessageDialog(null, "Patrón de exp reg incorrecto",
                                "Mailing", JOptionPane.ERROR_MESSAGE);
                    }
                }
            }

            @Override
            public void keyPressed(KeyEvent arg0) {
            }

            @Override
            public void keyReleased(KeyEvent arg0) {
            }
        });
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        buttonGroup1 = new javax.swing.ButtonGroup();
        jScrollPane1 = new javax.swing.JScrollPane();
        tablaResultados = new javax.swing.JTable();
        cboDesarrollo = new javax.swing.JComboBox();
        jSeparator1 = new javax.swing.JSeparator();
        lblDesarrollos = new javax.swing.JLabel();
        lblOpciones = new javax.swing.JLabel();
        cmdEnviarCorreo = new javax.swing.JButton();
        lblEstadoCliente = new javax.swing.JLabel();
        cboEstatus = new javax.swing.JComboBox();
        jPanel1 = new javax.swing.JPanel();
        optDesarrollo = new javax.swing.JRadioButton();
        optCorretaje = new javax.swing.JRadioButton();
        optEstatus = new javax.swing.JRadioButton();
        cmdBuscar = new javax.swing.JButton();
        panelFiltro = new javax.swing.JPanel();
        txtFiltro = new javax.swing.JTextField();

        setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
        setTitle("Mailing");

        tablaResultados.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Cliente", "Correo", "Estado", "Vive en"
            }
        ) {
            boolean[] canEdit = new boolean [] {
                false, false, false, false
            };

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        jScrollPane1.setViewportView(tablaResultados);

        cboDesarrollo.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));

        jSeparator1.setOrientation(javax.swing.SwingConstants.VERTICAL);

        lblDesarrollos.setText("Desarrollos");

        lblOpciones.setFont(new java.awt.Font("Ubuntu", 3, 18)); // NOI18N
        lblOpciones.setText("Opciones");

        cmdEnviarCorreo.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Imagenes/E-mail-32.png"))); // NOI18N
        cmdEnviarCorreo.setMnemonic('E');
        cmdEnviarCorreo.setText("Enviar correo");
        cmdEnviarCorreo.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                cmdEnviarCorreoActionPerformed(evt);
            }
        });

        lblEstadoCliente.setText("Estatus");

        cboEstatus.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Suspecto", "Prospecto", "Potencial" }));

        jPanel1.setBorder(javax.swing.BorderFactory.createTitledBorder("Buscar según"));

        optDesarrollo.setText("Desarrollo");

        optCorretaje.setText("Corretaje");

        optEstatus.setText("Estatus");

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addComponent(optEstatus)
                        .addGap(0, 0, Short.MAX_VALUE))
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(optCorretaje)
                            .addComponent(optDesarrollo))
                        .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))))
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addComponent(optDesarrollo)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(optCorretaje)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addComponent(optEstatus))
        );

        cmdBuscar.setIcon(new javax.swing.ImageIcon(getClass().getResource("/Imagenes/Busca.png"))); // NOI18N
        cmdBuscar.setMnemonic('B');
        cmdBuscar.setText("Buscar");
        cmdBuscar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                cmdBuscarActionPerformed(evt);
            }
        });

        panelFiltro.setBorder(javax.swing.BorderFactory.createTitledBorder("Filtro"));

        txtFiltro.setFont(new java.awt.Font("Ubuntu", 2, 18)); // NOI18N

        javax.swing.GroupLayout panelFiltroLayout = new javax.swing.GroupLayout(panelFiltro);
        panelFiltro.setLayout(panelFiltroLayout);
        panelFiltroLayout.setHorizontalGroup(
            panelFiltroLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(panelFiltroLayout.createSequentialGroup()
                .addContainerGap()
                .addComponent(txtFiltro)
                .addContainerGap())
        );
        panelFiltroLayout.setVerticalGroup(
            panelFiltroLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(panelFiltroLayout.createSequentialGroup()
                .addGap(25, 25, 25)
                .addComponent(txtFiltro, javax.swing.GroupLayout.DEFAULT_SIZE, 44, Short.MAX_VALUE)
                .addContainerGap())
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 716, Short.MAX_VALUE)
                    .addComponent(panelFiltro, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jSeparator1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(cmdEnviarCorreo, javax.swing.GroupLayout.PREFERRED_SIZE, 166, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                            .addComponent(lblDesarrollos, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(lblEstadoCliente))
                        .addGap(18, 18, 18)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(cmdBuscar, javax.swing.GroupLayout.PREFERRED_SIZE, 122, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                .addComponent(cboDesarrollo, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(cboEstatus, javax.swing.GroupLayout.PREFERRED_SIZE, 209, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                    .addComponent(lblOpciones, javax.swing.GroupLayout.PREFERRED_SIZE, 98, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jSeparator1)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(lblOpciones, javax.swing.GroupLayout.PREFERRED_SIZE, 41, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(18, 18, 18)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(cboDesarrollo, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(lblDesarrollos))
                        .addGap(18, 18, 18)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(lblEstadoCliente)
                            .addComponent(cboEstatus, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGap(59, 59, 59)
                        .addComponent(cmdBuscar)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 74, Short.MAX_VALUE)
                        .addComponent(cmdEnviarCorreo, javax.swing.GroupLayout.PREFERRED_SIZE, 47, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addContainerGap())
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(panelFiltro, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void cmdBuscarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cmdBuscarActionPerformed
        if (optEstatus.isSelected()) {
            try {
                modeloTabla.realizarConsultaEstatus(cboEstatus.getSelectedItem().toString());
            } catch (SQLException ex) {
                ex.printStackTrace();
                modeloTabla.desconectarDeBaseDeDatos();
                System.exit(1);
            } catch (IllegalStateException ex) {
                ex.printStackTrace();
                modeloTabla.desconectarDeBaseDeDatos();
                System.exit(1);
            }
        } else if (optDesarrollo.isSelected()) {
            try {
                modeloTabla.realizarConsultaDesarrollo(cboDesarrollo.getSelectedItem().toString());
            } catch (SQLException ex) {
                ex.printStackTrace();
                modeloTabla.desconectarDeBaseDeDatos();
                System.exit(1);
            } catch (IllegalStateException ex) {
                ex.printStackTrace();
                modeloTabla.desconectarDeBaseDeDatos();
                System.exit(1);
            }
        } else if (optCorretaje.isSelected()) {
            try {
                modeloTabla.realizarConsultaCorretaje();
            } catch (SQLException ex) {
                ex.printStackTrace();
                modeloTabla.desconectarDeBaseDeDatos();
                System.exit(1);
            } catch (IllegalStateException ex) {
                ex.printStackTrace();
                modeloTabla.desconectarDeBaseDeDatos();
                System.exit(1);
            }
        }
    }//GEN-LAST:event_cmdBuscarActionPerformed

    private void cmdEnviarCorreoActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cmdEnviarCorreoActionPerformed

        txtFiltro.setText("");
        sorter.setRowFilter(null);

        correos = obtenerCorreosSeleccionados();

        if (correos.size() > 0) {
            enviarCorreoElectronico();
        } else {

            JOptionPane.showMessageDialog(null, "No se seleccionó ninguna dirección"
                    + "", "Enviar correo", JOptionPane.INFORMATION_MESSAGE);

        }
    }//GEN-LAST:event_cmdEnviarCorreoActionPerformed

    private void obtenerDesarrollos() {

        cboDesarrollo.removeAllItems();

        List<Desarrollo> resultadoDesarrollos;
        resultadoDesarrollos = (new ConsultaDesarrollos()).consultarDesarrollos();

        for (int x = 0; x < resultadoDesarrollos.size(); x++) {
            cboDesarrollo.addItem(resultadoDesarrollos.get(x).obtenerNombre());
        }
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.ButtonGroup buttonGroup1;
    private javax.swing.JComboBox cboDesarrollo;
    private javax.swing.JComboBox cboEstatus;
    private javax.swing.JButton cmdBuscar;
    private javax.swing.JButton cmdEnviarCorreo;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JSeparator jSeparator1;
    private javax.swing.JLabel lblDesarrollos;
    private javax.swing.JLabel lblEstadoCliente;
    private javax.swing.JLabel lblOpciones;
    private javax.swing.JRadioButton optCorretaje;
    private javax.swing.JRadioButton optDesarrollo;
    private javax.swing.JRadioButton optEstatus;
    private javax.swing.JPanel panelFiltro;
    private javax.swing.JTable tablaResultados;
    private javax.swing.JTextField txtFiltro;
    // End of variables declaration//GEN-END:variables

    private ArrayList<String> obtenerCorreosSeleccionados() {

        int[] filasSeleccionadas = tablaResultados.getSelectedRows();
        ArrayList<String> correosObtenidos = new ArrayList<String>();

        if (filasSeleccionadas.length > 0) {
            for (int x = 0; x < filasSeleccionadas.length; x++) {
                if (!modeloTabla.getValueAt(filasSeleccionadas[x], 1).toString().equals("")) {
                    correosObtenidos.add(modeloTabla.getValueAt(filasSeleccionadas[x], 1).toString());
                }
            }
        }
        return correosObtenidos;
    }

    private void enviarCorreoElectronico() {
        String direcciones = "";

        for (int x = 0; x < correos.size(); x++) {
            direcciones += correos.get(x) + ",";
        }

        Desktop desktop = null;

        if (Desktop.isDesktopSupported()) {

            desktop = Desktop.getDesktop();

            try {

                String comando = "mailto:" + direcciones + "?subject=Boletín%20Eggo";
                URI uri = URI.create(comando);
                desktop.mail(uri);

            } catch (IOException ex) {
                ex.printStackTrace();
                JOptionPane.showMessageDialog(null, "Ocurrió un problema al enviar "
                        + "el correo electrónico", "Enviar correo", JOptionPane.ERROR_MESSAGE);
            } catch (IllegalArgumentException ex2) {
                JOptionPane.showMessageDialog(null, "Dirección de correo no válida",
                        "Enviar correo", JOptionPane.WARNING_MESSAGE);
            }
        }

    }
}

class ModeloTablaClientes extends AbstractTableModel {

    private final String[] NOMBRE_COLUMNAS = {"idCliente", "Nombre", "Correo",
        "Fecha de nacimiento", "Edad"};
    private PreparedStatement obtenerPorDesarrollo;
    private PreparedStatement obtenerPorEstatus;
    private PreparedStatement obtenerCorretaje;
    private Connection conexion;
    private ResultSet conjuntoResultados;
    private ResultSetMetaData metaDatos;
    private int numeroDeFilas;
    private boolean conectadoABaseDatos = false;

    ModeloTablaClientes() {

        conexion = FrmPrincipal.conexion;
        conectadoABaseDatos = true;

        try {

            obtenerPorEstatus = conexion.prepareStatement("SELECT Nombre, "
                    + "EMail, Estatus FROM Clientes WHERE Estatus=?");

            obtenerCorretaje = conexion.prepareStatement("SELECT Clientes.Nombre, "
                    + "Clientes.EMail, Producto.Nombre FROM Clientes JOIN "
                    + "ClientesXProducto USING (idCliente) JOIN Producto "
                    + "USING (idProducto) WHERE Producto.Desarrollo = false");


            obtenerPorDesarrollo = conexion.prepareStatement("SELECT Clientes.Nombre, "
                    + "Clientes.EMail, Desarrollos.Nombre AS 'Desarrollo' "
                    + "FROM Clientes JOIN ClientesXProducto USING (idCliente) "
                    + "JOIN Producto USING (idProducto) JOIN Desarrollos "
                    + "USING (idDesarrollos) WHERE Desarrollos.Nombre = "
                    + "?");

            realizarConsultaEstatus("Suspecto");

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void realizarConsultaEstatus(String status) throws SQLException, IllegalStateException {

        if (!conectadoABaseDatos) {
            throw new IllegalStateException("No hay conexión a la base de datos");
        }

        obtenerPorEstatus.setString(1, status);
        conjuntoResultados = obtenerPorEstatus.executeQuery();

        metaDatos = conjuntoResultados.getMetaData();

        conjuntoResultados.last();
        numeroDeFilas = conjuntoResultados.getRow();

        fireTableStructureChanged();
    }

    public void realizarConsultaDesarrollo(String desarrollo) throws SQLException, IllegalStateException {

        if (!conectadoABaseDatos) {
            throw new IllegalStateException("No hay conexión a la base de datos");
        }

        obtenerPorDesarrollo.setString(1, desarrollo);
        conjuntoResultados = obtenerPorDesarrollo.executeQuery();

        metaDatos = conjuntoResultados.getMetaData();

        conjuntoResultados.last();
        numeroDeFilas = conjuntoResultados.getRow();

        fireTableStructureChanged();
    }

    public void realizarConsultaCorretaje() throws SQLException, IllegalStateException {

        if (!conectadoABaseDatos) {
            throw new IllegalStateException("No hay conexión a la base de datos");
        }

        conjuntoResultados = obtenerCorretaje.executeQuery();

        metaDatos = conjuntoResultados.getMetaData();

        conjuntoResultados.last();
        numeroDeFilas = conjuntoResultados.getRow();

        fireTableStructureChanged();
    }

    @Override
    public Class getColumnClass(int columna) throws IllegalStateException {

        if (!conectadoABaseDatos) {
            throw new IllegalStateException("No hay conexión a la base de datos");
        }

        try {
            String nombreClase = metaDatos.getColumnClassName(columna + 1);

            return Class.forName(nombreClase);
        } catch (Exception excepcion) {
            excepcion.printStackTrace();
        }

        return Object.class;
    }

    @Override
    public int getColumnCount() throws IllegalStateException {

        if (!conectadoABaseDatos) {
            throw new IllegalStateException("No hay conexión a la base de datos");
        }

        try {
            return metaDatos.getColumnCount();
        } catch (SQLException excepcionSql) {
            excepcionSql.printStackTrace();
        }
        return 0;
    }

    @Override
    public String getColumnName(int columna) throws IllegalStateException {

        if (!conectadoABaseDatos) {
            throw new IllegalStateException("No hay conexión a la base de datos");
        }

        try {
            return metaDatos.getColumnName(columna + 1);

        } catch (Exception excepcion) {
            excepcion.printStackTrace();
        }

        return "";
    }

    @Override
    public int getRowCount() throws IllegalStateException {

        if (!conectadoABaseDatos) {
            throw new IllegalStateException("No hay conexión a la base de datos");
        }

        return numeroDeFilas;
    }

    @Override
    public Object getValueAt(int fila, int columna) throws IllegalStateException {

        if (!conectadoABaseDatos) {
            throw new IllegalStateException("No hay conexión a la base de datos");
        }

        try {
            conjuntoResultados.absolute(fila + 1);
            return conjuntoResultados.getObject(columna + 1);
        } catch (SQLException excepcionSql) {
            excepcionSql.printStackTrace();
        }

        return "";
    }

    public void desconectarDeBaseDeDatos() {
        if (conectadoABaseDatos) {

            try {
                conjuntoResultados.close();
                conexion.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                conectadoABaseDatos = false;
            }
        }
    }
}
